/***************************************************************************
 * Copyright (C) 2005 Global Biodiversity Information Facility Secretariat.
 * All Rights Reserved.
 *
 * The contents of this file are subject to the Mozilla Public
 * License Version 1.1 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a copy of
 * the License at http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS
 * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 * implied. See the License for the specific language governing
 * rights and limitations under the License.
 ***************************************************************************/
package org.gbif.wdpa.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.ArrayList;
import org.apache.commons.lang.StringUtils;

import org.gbif.wdpa.dao.WDPADao;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.gbif.wdpa.model.*;
import org.gbif.wdpa.utils.*;

/**
 * This Data Access Object (DAO) performs all the data gathering from the PostGIS database.
 * The SQL statements on it are highly coupled to PostGIS/
 * 
 * @author jatorre
 */
public class WDPADaoImpl extends JdbcDaoSupport implements WDPADao {
	protected StringRowMapper srm = new StringRowMapper();
	protected DataProviderRowMapper dataProviderRowMapper = new DataProviderRowMapper();
	
	
	/**
	 * return a List of data providers who has data for the area. The DataProvider object has
	 * metadata about the provider together with stats about how much data it has for this particular
	 * area.
	 * @author jatorre
	 */
	@SuppressWarnings("unchecked")
	public List<DataProvider> getAreaDataProviders(int areaId) {
		return getJdbcTemplate().query(
			"select pdr.*,dr.name as data_resource_name, dp.name as data_provider_name " +
			"from ((polygon_data_resource as pdr left join data_provider as dp on pdr.data_provider_id=dp.id) " +
			"left join data_resource as dr on dr.id=pdr.data_resource_id) where polygon_id=?",new Object[]{areaId}, dataProviderRowMapper);		
	}
	
	/**
	 * get the taxa list and stats for an area.
	 * The return Map has the following keys:
	 * 
	 * taxonStats 		= list of TaxonStats objects for area
	 * numObservation	= total number of observations on the area
	 * numOtherBor		= total number of other Basis Of Record that are not observations
	 * 					  and specimens
	 * numSpecimens		= total number of specimens
	 * total			= the sum of the previous ones.
	 * 
	 * @author jatorre
	 */	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getAreaTaxaStats(int areaId) {
		
		//We create a new instance of TaxonStatRowMapper so that to collect stats summing on each
		//record.
		TaxonStatRowMapper tsrm = new TaxonStatRowMapper();
		//This SQL statement handles the cases when the taxonomic group is null and replace it by the
		//unkown string.
		String sql = "select pc.taxon_id," +
				"CASE WHEN kingdom_name IS NULL THEN 'Unknown' ELSE kingdom_name END," +
				"CASE WHEN phylum_name IS NULL THEN 'Unknown' ELSE phylum_name END," +
				"CASE WHEN class_name IS NULL THEN 'Unknown' ELSE class_name END," +
				"CASE WHEN order_name IS NULL THEN 'Unknown' ELSE order_name END," +
				"CASE WHEN family_name IS NULL THEN 'Unknown' ELSE family_name END," +
				"CASE WHEN genus_name IS NULL THEN 'Unknown' ELSE genus_name END," +
				"CASE WHEN scientific_name IS NULL THEN 'Unknown' ELSE scientific_name END," +
				"num_specimens,num_observations,num_other_bor, (num_specimens+num_observations+num_other_bor) as total " +
				"from polygon_classification as pc inner join classification as c on pc.taxon_id=c.id where polygon_id=?";

		Map<String,Object> taxaStats = new HashMap();
		taxaStats.put("taxonStats", getJdbcTemplate().query(sql,new Object[]{areaId}, tsrm));
		taxaStats.put("numObservation", tsrm.totalNumObservations);
		taxaStats.put("numOtherBor", tsrm.totalNumOtherBor);
		taxaStats.put("numSpecimens", tsrm.totalNumSpecimens);
		taxaStats.put("total", tsrm.totalTotal);
		
		return taxaStats;
	}
	
	/**
	 * Sorry, no documnetation still.
	 * 
	 * @author jatorre
	 */
	@SuppressWarnings("unchecked")
	public Map<String,Object> getTileTaxonomy(int x,int y, int zoom, int areaId) {
		
		String sql = "select c.id as taxon_id,CASE WHEN kingdom_name IS NULL THEN 'Unknown' ELSE kingdom_name END," +
				"CASE WHEN phylum_name IS NULL THEN 'Unknown' ELSE phylum_name END,CASE WHEN class_name IS NULL THEN 'Unknown' ELSE class_name END," +
				"CASE WHEN order_name IS NULL THEN 'Unknown' ELSE order_name END,CASE WHEN family_name IS NULL THEN 'Unknown' ELSE family_name END," +
				"CASE WHEN genus_name IS NULL THEN 'Unknown' ELSE genus_name END,CASE WHEN scientific_name IS NULL THEN 'Unknown' ELSE scientific_name END, " +
				"count(*) as total,0 as num_observation, 0 as num_specimens, 0 as num_other_bor, 0 as num_observations,0 as num_otherBor, 0 as num_specimens " +
				"from occurrence as o inner join site as s on o.site_id=s.id inner join classification as c on o.taxon_id=c.id " +
				"where s.id in(select id from site as s inner join site_polygon as sp on s.id=sp.site_id where tile"+(zoom+2)+" @> '{"+x+","+y+"}' and polygon_id="+areaId+")" +
				"group by c.id,kingdom_name,phylum_name,class_name,order_name,family_name,genus_name,scientific_name";
		

		//logger.info(sql);
		TaxonStatRowMapper tsrm = new TaxonStatRowMapper();
		Map<String,Object> taxaStats = new HashMap();
		taxaStats.put("taxonStats", getJdbcTemplate().query(sql, tsrm));
		taxaStats.put("numObservation", tsrm.totalNumObservations);
		taxaStats.put("numOtherBor", tsrm.totalNumOtherBor);
		taxaStats.put("numSpecimens", tsrm.totalNumSpecimens);
		taxaStats.put("total", tsrm.totalTotal);
		
		
		return taxaStats;
	}

	
	@SuppressWarnings("unchecked")
	public List getAreaCellsByTileArea(Integer tileX,Integer tileY, Integer zoom, Integer polygonId) {	
		//long time=System.currentTimeMillis();
		String sql;
//		if (zoom>8) {
//		sql = "select tilex,tiley,tp.num_occ," +
//				"CASE WHEN tp.num_specimens IS NULL THEN 0 ELSE tp.num_specimens END,CASE WHEN tp.num_observations IS NULL THEN 0 ELSE tp.num_observations END," +
//				"CASE WHEN tp.num_other_bor IS NULL THEN 0 ELSE tp.num_other_bor END,CASE WHEN tp.num_plantae IS NULL THEN 0 ELSE tp.num_plantae END," +
//				"CASE WHEN tp.num_animalia IS NULL THEN 0 ELSE tp.num_animalia END,CASE WHEN tp.num_other_kingdoms IS NULL THEN 0 ELSE tp.num_other_kingdoms END," +
//				"CASE WHEN ST_Covers(p.the_geom,get_google_tile_polygon(tilex,tiley,"+(zoom+2)+")) THEN '' " +
//						"ELSE astext(ST_GeometryN(ST_Multi(ST_Intersection(p.the_geom,get_google_tile_polygon(tilex,tiley,"+(zoom+2)+"))),1)) END  as border_geom " +
//				"from tile"+zoom+"polygon as tp inner join polygon as p on tp.polygon_id=p.id WHERE tileorigx="+tileX+" AND tileorigy="+tileY +" AND polygon_id="+polygonId;
//		}else {
			sql = "select tilex,tiley,tp.num_occ," +
			"CASE WHEN tp.num_specimens IS NULL THEN 0 ELSE tp.num_specimens END,CASE WHEN tp.num_observations IS NULL THEN 0 ELSE tp.num_observations END," +
			"CASE WHEN tp.num_other_bor IS NULL THEN 0 ELSE tp.num_other_bor END,CASE WHEN tp.num_plantae IS NULL THEN 0 ELSE tp.num_plantae END," +
			"CASE WHEN tp.num_animalia IS NULL THEN 0 ELSE tp.num_animalia END,CASE WHEN tp.num_other_kingdoms IS NULL THEN 0 ELSE tp.num_other_kingdoms END," +
			"'' as border_geom " +
			"from tile"+zoom+"polygon as tp inner join polygon as p on tp.polygon_id=p.id WHERE tileorigx="+tileX+" AND tileorigy="+tileY +" AND polygon_id="+polygonId;
			
		//}
		
		List<Map> tl = getJdbcTemplate().queryForList(sql);
		//logger.info(sql);

		for (Map<String,Object> cell : tl) {
			String border_geom = (String)cell.get("border_geom");
			if(border_geom !=null && border_geom.length()>0) {

				Map<String,Object> completeEndodedpolygon = new HashMap();
				String po = border_geom.replace("POLYGON((","");
				String[] paths = po.split("\\)");
				String outterRing = paths[0];
				String[] points = outterRing.split( ",\\s*" );
				Track outterPoli = new Track();
				for ( String point : points ) {
					String[] coords = point.split(" ");
					outterPoli.addTrackpoint(new Trackpoint(new Double(coords[1]), new Double(coords[0])));
				
				}
				completeEndodedpolygon.put("outterRing", PolylineEncoder.createEncodings(outterPoli, 17, 1));	
				cell.put("border_geom", PolylineEncoder.createEncodings(outterPoli, 17, 1));
			}
		}
//		if ((System.currentTimeMillis()-time)>800) {
//			logger.info(sql +": " + (System.currentTimeMillis()-time) + " msecs");
//		}
		return tl;
		
	}		
	
	@SuppressWarnings("unchecked")
	public List getTileLimits(Integer zoom, Integer polygonId) {		
		
		//we should be using there the cache.
		String sql = "select floor(num_occ/50)*50 as low , (floor(num_occ/50)+1)*50 as up from tile"+(zoom)+"polygon where polygon_id="+polygonId+" group by floor(num_occ/50),polygon_id order by floor(num_occ/50)";		
		return getJdbcTemplate().queryForList(sql);
		
	}			
	
	@SuppressWarnings("unchecked")
	public List getAreasBasicData(String isoCountry) {
		
		String sql = "select name,id from polygon where country_iso_code='"+isoCountry+"' order by name";
		return getJdbcTemplate().queryForList(sql);
	}
	
	@SuppressWarnings("unchecked")
	public List getAreasByCoordinates(float lat,float lng) {
		String sql = "select name,id from polygon where intersects(setsrid(makepoint("+lng+","+lat+"),4326), the_geom) =true AND setsrid(makepoint("+lng+","+lat+"),4326) && the_geom";
		return getJdbcTemplate().queryForList(sql);
	}	

	
	@SuppressWarnings("unchecked")
	public List getAreasByIds(Integer [] ids) {
		String sql = "select id,name,country,desig_eng,num_species,num_occ,num_providers,num_resources,num_plantae,num_animalia," +
				"east,west,north,south,"+
				"num_other_kingdoms,num_specimens,num_observations,num_other_bor,country_iso_code," +
				"astext((ST_Dump(geom_simplified)).geom) as geom " +
				"from polygon " +
				"where id in ("+StringUtils.join(ids,",")+")";
		
		//this returns all the polygons in WKT format for the area.
		SqlRowSet rs =	getJdbcTemplate().queryForRowSet(sql);
				
		int currentAreaId = 0;
		List areas = new ArrayList();
		Map<String,Object> currentArea = new HashMap();
		List polis = new ArrayList();
		while(rs.next()){
			
			if (currentAreaId != rs.getInt("id")) {
				
				if (currentAreaId!=0) {
					currentArea.put("polygons",polis);
					areas.add(currentArea);
					
					currentArea = new HashMap();
					polis = new ArrayList();
				}
				
				currentArea.put("name", rs.getString("name"));
				currentArea.put("country", rs.getString("country"));
				currentArea.put("isoCode", rs.getString("country_iso_code"));
				currentArea.put("desig_eng", rs.getString("desig_eng"));
				currentArea.put("numspecies", rs.getInt("num_species"));
				currentArea.put("numocc", rs.getInt("num_occ"));
				currentArea.put("num_providers", rs.getInt("num_providers"));
				currentArea.put("num_resources", rs.getInt("num_resources"));
				currentArea.put("num_plantae", rs.getInt("num_plantae"));
				currentArea.put("num_animalia", rs.getInt("num_animalia"));
				currentArea.put("num_other_kingdoms", rs.getInt("num_other_kingdoms"));
				currentArea.put("num_specimens", rs.getInt("num_specimens"));
				currentArea.put("num_observation", rs.getInt("num_observations"));
				currentArea.put("num_other_bor", rs.getInt("num_other_bor"));
				currentArea.put("north", rs.getFloat("north"));
				currentArea.put("south", rs.getFloat("south"));
				currentArea.put("east", rs.getFloat("east"));
				currentArea.put("west", rs.getFloat("west"));
				currentArea.put("id", rs.getInt("id"));
				currentAreaId = rs.getInt("id");
				
			}
					
			Map<String,Object> completeEndodedpolygon = new HashMap();
			String po = rs.getString("geom").replace("POLYGON((","");
			String[] paths = po.split("\\)");
			String outterRing = paths[0];
			String[] points = outterRing.split( ",\\s*" );
			Track outterPoli = new Track();
			for ( String point : points ) {
				String[] coords = point.split(" ");
				outterPoli.addTrackpoint(new Trackpoint(new Double(coords[1]), new Double(coords[0])));
			}
			completeEndodedpolygon.put("outterRing", PolylineEncoder.createEncodings(outterPoli, 17, 1));			
			List innerRings = new ArrayList();
			for(int i=1;i<paths.length;i++){
				points = paths[i].substring(2).split( ",\\s*" );
				Track innerPoli = new Track();
				for ( String point : points ) {
					String[] coords = point.split(" ");
					innerPoli.addTrackpoint(new Trackpoint(new Double(coords[1]), new Double(coords[0])));
				}	
				innerRings.add(PolylineEncoder.createEncodings(innerPoli, 17, 1));
			}                                
			
			completeEndodedpolygon.put("innerRings", innerRings);
			polis.add(completeEndodedpolygon);
			
		}		
		
		currentArea.put("polygons",polis);
		areas.add(currentArea);		
		return areas;		
	}
	
	
	
	//------------ ROW MAPPERS -------------------------
	
	
	
	/**
	 * Simple row mapper that will get the String from the result
	 * @author trobertson
	 */
	class StringRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
			return rs.getString("polygon_name");
		}
		
	}
	
	/**
	 * Row mapper to get DataProvider objects from a query result
	 * @author jatorre
	 */	
	class DataProviderRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
			DataProvider dp = new DataProvider();
			dp.id = rs.getInt("data_provider_id");
			dp.name = rs.getString("data_provider_name");
			dp.resourceName = rs.getString("data_resource_name");
			dp.resourceId = rs.getInt("data_resource_id");
			dp.numOcc = rs.getInt("num_occ");
			dp.numSpecies = rs.getInt("num_species");
			dp.numSpecimens = rs.getInt("num_specimens");
			dp.numObservations = rs.getInt("num_observations");
			dp.numOtheBor = rs.getInt("num_other_bor");
			dp.numPlantae = rs.getInt("num_plantae");
			dp.numAnimalia = rs.getInt("num_animalia");
			dp.numOtherKingdoms = rs.getInt("num_other_kingdoms");
			
			return dp;
		}
	}
	
	/**
	 * Row mapper to get TaxonStats for a query result.
	 * It sums the number of observation, specimens, etc while walking through the recordset
	 * to add it later as stats to the returned object
	 * @author jatorre
	 */
	class TaxonStatRowMapper implements RowMapper {
		
		private int totalNumObservations = 0;
		private int totalNumSpecimens = 0;
		private int totalNumOtherBor =0;
		private int totalTotal=0;
		
		public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
			TaxonStats ts = new TaxonStats();
			
			ts.clas = rs.getString("class_name");
			ts.family = rs.getString("family_name");
			ts.genus = rs.getString("genus_name");
			ts.kingdom = rs.getString("kingdom_name");
			ts.order = rs.getString("order_name");
			ts.phylum = rs.getString("phylum_name");
			ts.species = rs.getString("scientific_name");
			ts.numObservations = rs.getInt("num_observations");
			ts.numSpecimens = rs.getInt("num_specimens");
			ts.numOtherBor = rs.getInt("num_other_bor");
			ts.total = rs.getInt("total");
			ts.taxonId = rs.getInt("taxon_id");

			//Sum to the totals
			totalNumObservations +=ts.numObservations;
			totalNumSpecimens +=ts.numSpecimens;
			totalNumOtherBor +=ts.numOtherBor;
			totalTotal +=ts.total;
			return ts;
		}
		
	}	
	
	
	
	
	
}
